package com.pcsky.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.LinkedList;
import java.util.List;
import java.util.Vector;

import com.pcsky.model.CustomerRecorder;

public class CustomerRecorderDao {
	private Connection connection;
	public void insert( CustomerRecorder bean){
		if ( connection != null ) {
			Statement stat;
			try {
				int prevkey = getLastKey( bean.getAmmeterid());
				String sql = "insert into customerRecorder (meternumber,powernumber,ammeterid,recordDate,lastMonthId,factNumber,memo) values (?,?,?,?,?,?,?) ";
				PreparedStatement ps = connection.prepareStatement(sql);
				ps.setDouble(1, bean.getMeternumber() );
				ps.setDouble(2, bean.getPowernumber());
				ps.setInt(3, bean.getAmmeterid());
				ps.setLong(4,  bean.getRecordDate() );
				ps.setInt(5, prevkey);
				ps.setDouble(6, bean.getFactNumber());
				ps.setString(7, bean.getMemo());
				bean.setLastMonthId(prevkey);
				ps.execute();
				stat = connection.createStatement();
				ResultSet rs=stat.executeQuery("SELECT last_insert_rowid() as last_insert_rowid");
				if(rs.next()) {  
					bean.setId(rs.getInt("last_insert_rowid"));  
				}
				rs.close();
				ps.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
 
		}
	}
	private int getLastKey( int ammeterid){
		int rtv = 0;
		String sql = "select id from customerrecorder  where  recorddate  in (select max(recorddate) as newdate from customerrecorder where  ammeterid="+ammeterid+")";
		if ( connection != null ) {
			try {
				ResultSet rs = connection.createStatement().executeQuery(sql);
				if (rs.next())
						rtv = rs.getInt("id");
				rs.close();
			}catch (Exception e) {
				e.printStackTrace();
			}
		}	
		return rtv; 
	}
	public void getLastRecordDate( int ammeterid ) {
		String sql = " select max(recorddate) as newdate from customerrecorder where  ammeterid="+ammeterid;
		if ( connection != null ) {
			try {
				ResultSet rs = connection.createStatement().executeQuery(sql);
				rs.next();
				String DATE_FORMAT_SHORT = "yyyy-MM-dd";
				SimpleDateFormat sdf = new SimpleDateFormat(DATE_FORMAT_SHORT);
				System.out.println( sdf.format( rs.getDate("newdate")));
				rs.close();
			}catch (Exception e) {
				e.printStackTrace();
			}
		}
	}
	
	public double getLastReadNumber ( int ammeterid ) {
		double rtv= 0d;
		if ( connection != null ) {
			try {
//				String sql = " select max(recorddate) as newdate from customerrecorder where  ammeterid="+ammeterid;
//				ResultSet rs = connection.createStatement().executeQuery(sql);
//				long datelong = 0;
				int lastRecordKey = getLastKey( ammeterid);
//				if (rs.next() ) {
//					datelong = rs.getDate("newdate").getTime();
					String sql = " select  meternumber from customerrecorder where ammeterid="+ammeterid + " and id=" + lastRecordKey;
					Statement stmt =  connection.createStatement();
					ResultSet rs =stmt.executeQuery(sql);
					if ( rs.next() )
							rtv =  rs.getDouble("meternumber");
//				}
				rs.close();
			}catch (Exception e) {
				System.out.println(e.toString());
			}
		}
		return rtv ;
	}
	
	public void update( CustomerRecorder bean){
		if ( connection != null ) {
			try {
				String sql = " update  customerRecorder set meternumber=?,powernumber=?,ammeterid=?,recordDate=? , factNumber=?,memo=? " +
						"  WHERE id=? ";
				PreparedStatement ps = connection.prepareStatement(sql);
				ps.setDouble(1, bean.getMeternumber() );
				ps.setDouble(2, bean.getPowernumber());
				ps.setInt(3, bean.getAmmeterid());
				ps.setLong(4,   bean.getRecordDate()  );
				ps.setDouble(5, bean.getPowernumber());
				ps.setString(6, bean.getMemo());
				ps.setInt(7, bean.getId() );
				ps.execute();				
				ps.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			
		}
	}
	public void delete( CustomerRecorder bean){
		if ( connection != null ) {
			Statement stat;
			try {
//				System.out.println( " delete from customerRecorder WHERE id="+bean.getId());
				stat = connection.createStatement();
				stat.executeUpdate(" delete from customerRecorder WHERE id="+bean.getId());
				stat.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			
		}
	}
	
 
	public Vector<Vector>  listAllVt(int ammeterKey){
		Vector<Vector> vt = new Vector<Vector>();
		if ( connection != null ) {
			Statement stat;
			SimpleDateFormat simpleFormate = new SimpleDateFormat( "yyyy-MM-dd" );
			try {
				stat = connection.createStatement()	;
				String sql = "select * from  customerRecorder where  ammeterid="+ammeterKey+ " order by id desc";
//				System.out.println( sql );
				ResultSet rs  =  stat.executeQuery(sql );
				while ( rs.next()) {
					Vector v = new Vector();
					v.add(rs.getInt("id"));
					v.add(rs.getInt("ammeterid"));
					v.add(rs.getInt("lastMonthId"));
					v.add(rs.getDouble("meternumber"));
					v.add(rs.getDouble("powernumber"));
					v.add( simpleFormate.format( rs.getDate("recordDate") )	);
					v.add(   rs.getDouble("factNumber")  	);
					v.add(   rs.getString("memo")  	);
					vt.add(v);
				}
				rs.close();
				stat.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return vt;
	}
	public Connection getConnection() {
		return connection;
	}
	public void setConnection(Connection connection) {
		this.connection = connection;
	}

	public double getLastReadNumber(int ammeterid, int key) {
		double rtv= 0d;
		if ( connection != null ) {
			try {
				String sql = " select meternumber from customerrecorder where ammeterid="+ammeterid + " and id="+key;
				ResultSet rs = connection.createStatement().executeQuery(sql);
				rs.next();
				rtv =  rs.getDouble("meternumber");
				rs.close();
				
			}catch (Exception e) {
				
			}
		}
		return rtv ;
	}
	public List<CustomerRecorder> getRecordByAmmeterId(int ammeterid,long dateBeginStr,long dateEndStr) {
		List<CustomerRecorder>  rtvs  = new LinkedList<CustomerRecorder>();
		if ( connection != null ) {
			Statement stat;
			try {
				stat = connection.createStatement()	;
				String sql = "select * from  customerRecorder where  ammeterid="+ammeterid+
						" and recordDate >="+dateBeginStr+" and recordDate<="+dateEndStr+" order by id asc";
//				System.out.println( sql );
				ResultSet rs  =  stat.executeQuery(sql );
				while ( rs.next()) {
					CustomerRecorder v = new CustomerRecorder();
					v.setId(rs.getInt("id"));
					v.setAmmeterid(rs.getInt("ammeterid") );
					v.setLastMonthId(rs.getInt("lastMonthId"));
					v.setMeternumber(rs.getDouble("meternumber"));
					v.setPowernumber(rs.getDouble("powernumber"));
					v.setRecordDate( rs.getLong("recordDate")	);
					v.setFactNumber(rs.getDouble("factNumber"));
					v.setMemo(rs.getString("memo"));
					rtvs.add(v);
				}
				rs.close();
				stat.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return rtvs;
	}
}
